IF OBJECT_ID('ssf.ptx_create_task_job') IS NOT NULL
	DROP PROCEDURE ssf.ptx_create_task_job
GO

CREATE PROCEDURE ssf.ptx_create_task_job
	@job_name NVARCHAR(128)
	, @task_database_name NVARCHAR(128) = NULL
	, @task_procedure_name NVARCHAR(256)
	, @task_parameter_list NVARCHAR(MAX) = NULL
	, @log_file_path NVARCHAR(200) = NULL
	, @job_id UNIQUEIDENTIFIER = NULL OUTPUT
AS
/*
<object_header>

<object_summary>
	<purpose><![CDATA[

	]]></purpose>
</object_summary>

</object_header>
*/

SET NOCOUNT ON

-- ============
/*<code_comment><![CDATA[declare dynamic sql variables]]></code_comment>*/
DECLARE	@proc_error INT
			, @sql_error INT
			, @error_message NVARCHAR(2048)
			, @error_line INT
			, @row_count BIGINT
			, @sql VARCHAR(8000) -- or (MAX)
			, @nsql NVARCHAR(4000) -- or (MAX)
			, @crlf CHAR(2) = CHAR(13) + CHAR(10)
			, @quote CHAR(1) = CHAR(39)
			, @tab CHAR(1) = CHAR(9)
-- ============

-- ============
/*<code_comment><![CDATA[declare local variables]]></code_comment>*/
DECLARE	@job_category NVARCHAR(128)
			, @job_owner NVARCHAR(128)
			, @step_command NVARCHAR(MAX)
-- ============

-- ============
/*<code_comment><![CDATA[check parameters]]></code_comment>*/
IF NULLIF(@job_name, N'') IS NULL
OR NULLIF(@task_procedure_name, N'') IS NULL
BEGIN
	RAISERROR('A required parameter is blank or null.', 16, 1)
	RETURN 1
END
-- ============

-- ============
/*<code_comment><![CDATA[initialize job_id output parameter]]></code_comment>*/
SET @job_id = NULL
-- ============

-- ============
/*<code_comment><![CDATA[begin transaction]]></code_comment>*/
BEGIN TRANSACTION

BEGIN TRY
-- ============

-- ============
/*<code_comment><![CDATA[delete job]]></code_comment>*/
EXECUTE ssf.job_delete
	@job_name = @job_name
-- ============

-- ============
/*<code_comment><![CDATA[add job]]></code_comment>*/
SET @job_category = N'[dynamic ptx job (Local)]'
SET @job_owner = SUSER_SNAME()

EXECUTE msdb.dbo.sp_add_job
	@job_name = @job_name
	, @category_name = @job_category
	, @owner_login_name = @job_owner
	, @job_id = @job_id OUTPUT
-- ============

-- ============
/*<code_comment><![CDATA[add job step]]></code_comment>*/
SET @task_database_name = ISNULL(NULLIF(@task_database_name, N''), DB_NAME())
SET @log_file_path = ISNULL(@log_file_path, N'')

SET @step_command = N'EXECUTE ' + @task_procedure_name
	+ CASE WHEN NULLIF(@task_parameter_list, N'') IS NOT NULL
		THEN @crlf + @tab + @task_parameter_list
		ELSE N''
		END

EXECUTE msdb.dbo.sp_add_jobstep
	@job_id = @job_id
	, @step_id = 1
	, @step_name = N'run_task'
	, @subsystem = N'TSQL'
	, @command = @step_command
	, @on_success_action = 1
	, @on_fail_action = 2
	, @database_name = @task_database_name
	, @output_file_name = @log_file_path
-- ============

-- ============
/*<code_comment><![CDATA[local server job]]></code_comment>*/
EXECUTE msdb.dbo.sp_add_jobserver
	@job_id = @job_id
	, @server_name = N'(LOCAL)'
-- ============

-- ============
/*<code_comment><![CDATA[end transaction]]></code_comment>*/
END TRY
BEGIN CATCH
	SELECT @sql_error = ERROR_NUMBER()
		, @error_message = ERROR_MESSAGE()
END CATCH

IF @sql_error <> 0
BEGIN
	ROLLBACK TRANSACTION
	
	RAISERROR('Failed to create the job <%s>:%s%s', 16, 1, @job_name, @crlf, @error_message)
	RETURN @sql_error
END

COMMIT TRANSACTION
-- ============

RETURN @@ERROR
GO
